package com.jeeplus.modules.tms.util;

import com.jeeplus.modules.ap.entity.Expenditure;
import com.jeeplus.modules.ap.entity.Imprest;
import com.jeeplus.modules.ap.entity.Income;
import com.jeeplus.modules.tms.entity.TmsWaybill;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * Created by lichaohui on 2018/4/26.
 */
public  class ExportTms {
    /**
     * 收入导出
     * @param response
     * @param list
     */
    public  void  exportbill(HttpServletResponse response,  List<TmsWaybill> list){
        //excel标题
        String[] title = {"项目","现场督导","调度人员","下单日期","发货日期",
                "订单类型","作业类型","提货单号","装运单号","合同号","租车协议号",
                "起运地","到站省", "结算地","合同里程","总件数","总毛重","运输单价",
                "总费用", "总车款","合同费用","预付现金","预付油卡","货到合同金额",
                "回执合同金额", "租车费用","防护品支出", "装卸费支出","其他支出","租车费用比例","收货单位名称",
                "收货单位地址","签收状态", "签收人","签收件数","防护品收入","信息费收入","保险费收入",
                "其它收入", "承运商","承运商联系人", "车牌号","车辆类型","行驶证号","司机姓名",
                "司机电话", "身份证号"};


        //excel文件名
        String fileName = "运单报表.xls";

        //sheet名
        String sheetName = "运单";
//创建HSSFWorkbook
        HSSFWorkbook wb = bill(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook bill(String sheetName,String []title,List<TmsWaybill> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象

            row.createCell(0).setCellValue(list.get(i).getProjectName());
            row.createCell(1).setCellValue(list.get(i).getGuidance().getName());
            row.createCell(2).setCellValue(list.get(i).getDispatchName().getName());
            row.createCell(3).setCellValue(sdf.format(list.get(i).getDate()));
            row.createCell(4).setCellValue(sdf.format(list.get(i).getFhrq()));
            row.createCell(5).setCellValue(list.get(i).getType());
            row.createCell(6).setCellValue(list.get(i).getWorkType());
            row.createCell(7).setCellValue(list.get(i).getTh());
            row.createCell(8).setCellValue(list.get(i).getShipmentNumbers());
            row.createCell(9).setCellValue(list.get(i).getCode());
            row.createCell(10).setCellValue(list.get(i).getForwardingPrice());
            row.createCell(11).setCellValue(list.get(i).getContractDistance());
            row.createCell(12).setCellValue(list.get(i).getArriveProvince());
            row.createCell(13).setCellValue(list.get(i).getArrive());
            row.createCell(14).setCellValue(list.get(i).getDistance());
            row.createCell(15).setCellValue(list.get(i).getZjs());
            row.createCell(16).setCellValue(list.get(i).getWeight());
            row.createCell(17).setCellValue(list.get(i).getYsdj());
            row.createCell(18).setCellValue(list.get(i).getZfy());
            row.createCell(19).setCellValue(list.get(i).getFreight());
            row.createCell(20).setCellValue(list.get(i).getTotalCost());
            row.createCell(21).setCellValue(list.get(i).getPaymentNumber());
            row.createCell(22).setCellValue(list.get(i).getLoadingJe());
            row.createCell(23).setCellValue(list.get(i).getKdPayment());
            row.createCell(24).setCellValue(list.get(i).getReceiptCalculate());
            row.createCell(25).setCellValue(list.get(i).getRentingCost());
            row.createCell(26).setCellValue(list.get(i).getFhpzc());
            row.createCell(27).setCellValue(list.get(i).getZxfzc());
            row.createCell(28).setCellValue(list.get(i).getQtzc());
            row.createCell(29).setCellValue(list.get(i).getZcfybl());
            row.createCell(30).setCellValue(list.get(i).getUnitName());
            row.createCell(31).setCellValue(list.get(i).getSite());
            row.createCell(32).setCellValue(list.get(i).getQszt());
            row.createCell(33).setCellValue(list.get(i).getQsr());
            row.createCell(34).setCellValue(list.get(i).getQsjs());
            row.createCell(35).setCellValue(list.get(i).getFhpsr());
            row.createCell(36).setCellValue(list.get(i).getXxfsr());
            row.createCell(37).setCellValue(list.get(i).getBxfsr());
            row.createCell(38).setCellValue(list.get(i).getQtsr());
            row.createCell(39).setCellValue(list.get(i).getCommonCarrier());
            row.createCell(40).setCellValue(list.get(i).getContacts());
            row.createCell(41).setCellValue(list.get(i).getPlateNumber());
            row.createCell(42).setCellValue(list.get(i).getVehicleType());
            row.createCell(43).setCellValue(list.get(i).getDrivingLicense());
            row.createCell(44).setCellValue(list.get(i).getDriverName());
            row.createCell(45).setCellValue(list.get(i).getDriverPhone());
            row.createCell(46).setCellValue(list.get(i).getDriveNumber());

        }
        return wb;
    }

    /**
     * 支出导出
     * @param response
     * @param list
     */
    public  void  exportexpenditure(HttpServletResponse response,  List<Expenditure> list){
        //excel标题
        String[] title = {"单号","项目","经办人","日期","科目","金额","支出类别","费用对象","费用归属","票据类型","票据号码","说明"};


        //excel文件名
        String fileName = "费用支出表.xls";

        //sheet名
        String sheetName = "费用支出";
//创建HSSFWorkbook
        HSSFWorkbook wb = expenditure(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook expenditure(String sheetName,String []title,List<Expenditure> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象

            row.createCell(0).setCellValue(list.get(i).getDocumentNumber());
            row.createCell(1).setCellValue(list.get(i).getProjectName().getName());
            row.createCell(2).setCellValue(list.get(i).getResponsiblePerson().getName());
            row.createCell(3).setCellValue(sdf.format(list.get(i).getDate()));
            row.createCell(4).setCellValue(list.get(i).getOneSubject());
            row.createCell(5).setCellValue(list.get(i).getMoney());
            row.createCell(6).setCellValue(list.get(i).getType());
            row.createCell(7).setCellValue(list.get(i).getCostObject());
            row.createCell(8).setCellValue(list.get(i).getCostAffiliation());
            row.createCell(9).setCellValue(list.get(i).getBillType());
            row.createCell(10).setCellValue(list.get(i).getBillNumber());
            row.createCell(11).setCellValue(list.get(i).getRemarks());

        }
        return wb;
    }



    /**
     * 备用金导出
     * @param response
     * @param list
     */
    public  void  exportimprest(HttpServletResponse response,  List<Imprest> list){
        //excel标题
        String[] title = {"申请日期","项目","申请人","单据号","申请理由","申请类别","申请金额","到账日期","到账金额","付款人"};


        //excel文件名
        String fileName = "备用金表.xls";

        //sheet名
        String sheetName = "备用金";
//创建HSSFWorkbook
        HSSFWorkbook wb = imprest(sheetName, title, list, null);

//响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建工作簿
    public static HSSFWorkbook imprest(String sheetName,String []title,List<Imprest> list, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for(int i=0;i<list.size();i++){
            row = sheet.createRow(i + 1);
            //将内容按顺序赋给对应的列对象

            row.createCell(0).setCellValue(sdf.format(list.get(i).getApplicationDate()));
            row.createCell(1).setCellValue(list.get(i).getProjectName());
            row.createCell(2).setCellValue(list.get(i).getApplicant());
            row.createCell(3).setCellValue(list.get(i).getNumber());
            row.createCell(4).setCellValue(list.get(i).getReason());
            row.createCell(5).setCellValue(list.get(i).getMoney());
            row.createCell(6).setCellValue(list.get(i).getTotalMoney());
            row.createCell(7).setCellValue(sdf.format(list.get(i).getDzrq()));
            row.createCell(8).setCellValue(list.get(i).getPaymentMoney());
            row.createCell(9).setCellValue(list.get(i).getPayer());
        }
        return wb;
    }

    //发送响应流方法
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"iso-8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
      }
